package nxu.it.movieapi.service.pg.dao;

import nxu.it.api.model.Filmography;
import org.jdbi.v3.sqlobject.config.RegisterBeanMapper;
import org.jdbi.v3.sqlobject.customizer.Bind;
import org.jdbi.v3.sqlobject.statement.SqlQuery;

import java.util.List;
import nxu.it.api.model.Cast;

public interface MovieCastPgDao {

    @SqlQuery("SELECT s.* FROM staff s " +
            "JOIN movie_cast mc ON s.id = mc.staff_id " +
            "WHERE mc.movie_id = :movieId")
    @RegisterBeanMapper(Cast.class)
    List<Cast> findCastsByMovieId(@Bind("movieId") Integer movieId);

    @SqlQuery("select t0.id, t0.english_name, t0.chinese_name," +
            "t0.avatar, t0.avatar_url, t1.act_as, t1.role_id AS role_type_id " +
            "from public.staff t0 inner join public.movie_cast t1 on t0.id = t1.staff_id " +
            "where t1.movie_id = :movieId and t1.role_id = :role")
    @RegisterBeanMapper(Cast.class)
    List<Cast> findCastsByMovieIdAndRole(@Bind("movieId") Integer movieId, @Bind("role") Integer role);

    @SqlQuery("SELECT m.* FROM movie m " +
            "JOIN movie_cast mc ON m.id = mc.movie_id " +
            "WHERE mc.staff_id = :staffId")
    @RegisterBeanMapper(Filmography.class)
    List<Filmography> findMoviesByStaffId(@Bind("staffId") Integer staffId);

    @SqlQuery("SELECT m.* FROM movie m " +
            "JOIN movie_cast mc ON m.id = mc.movie_id " +
            "WHERE mc.staff_id = :staffId AND mc.role_id = :role")
    @RegisterBeanMapper(Filmography.class)
    List<Filmography> findMoviesByStaffIdAndRole(@Bind("staffId") Integer staffId, @Bind("role") Integer role);
}
